# Define your item pipelines here
#
# Don't forget to add your pipeline to the ITEM_PIPELINES setting
# See: https://docs.scrapy.org/en/latest/topics/item-pipeline.html


# useful for handling different item types with a single interface
from itemadapter import ItemAdapter
from openpyxl import Workbook


class BookPipeline:

    def open_spider(self, spider):
        self.work = Workbook()
        self.w = self.work.active
        self.w.append(['题目', '作者', '出版社', '出版时间', '装订', '页数', '系列', '价格', '简介', 'ISBN', 'url'])

    def process_item(self, item, spider):
        # con = pymysql.connect(host="localhost", port=3306, user="root", password="123", db="books")
        # cursor = con.cursor()
        author = item["author"]
        publish = item["publish"]
        year = item["year"]
        page = item["page"]
        price = item["price"]
        make_up = item["make_up"]
        ISBN = item["ISBN"]
        book_title = item["book_title"]
        series = item["series"]
        abstract = item["abstract"]
        url = item["url"]
        self.w.append([book_title, author, publish, year, make_up, page, series, price, abstract, ISBN, url])
        # cursor.execute(
        #     f'insert into book values(0,{book_title},{author},{publish},{year},{page},{price},{make_up},{series},{ISBN},{abstract})')
        # con.commit()
        return item

    def close_spider(self, spider):
        self.work.save('books.xlsx')
        self.work.close()
